package com.huizu.quartz.service.impl;

import com.huizu.common.core.domain.entity.SysUser;
import com.huizu.common.oracleDb.OracleDbConnection;
import com.huizu.common.utils.SecurityUtils;
import com.huizu.common.utils.SnowFlake;
import com.huizu.common.utils.StringUtils;
import com.huizu.common.utils.dx.SecurityUtil;
import com.huizu.project.domain.*;
import com.huizu.project.mapper.*;
import com.huizu.quartz.domain.*;
import com.huizu.quartz.service.IDataSynchronizerService;
import com.huizu.system.service.ISysConfigService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Author: pjl
 * @Date: 2024/10/24
 * @ClassName: IDataSynchronizerServiceImpl
 * @Description: 数据同步
 */
@Service
@Slf4j
public class IDataSynchronizerServiceImpl implements IDataSynchronizerService {

    @Autowired
    private SysUserApiMapper sysUserApiMapper;

    @Autowired
    private ProXyMapper proXyMapper;

    @Autowired
    private ProNjMapper proNjMapper;

    @Autowired
    private ProZyMapper proZyMapper;

    @Autowired
    private ProBjMapper proBjMapper;

    @Autowired
    private ProUserMapper proUserMapper;

    @Autowired
    private ISysConfigService iSysConfigService;


    @Override
    public void everyDay() {
        Connection connection = null;
        try {
            // 获取Oracle数据库连接
            connection = OracleDbConnection.getConnection();
            // 学院同步
            xySynchronization(connection);
            njSynchronization(connection);
            zySynchronization(connection);
            bjSynchronization(connection);
            studentSynchronization(connection);
            teacherSynchronization(connection);
        }  catch (SQLException e) {
            log.error(e.getMessage());
            e.printStackTrace();
            // 处理异常
        } finally {
            // 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error(e.getMessage());
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void xySynchronization() {
        Connection connection = null;
        try {
            connection = OracleDbConnection.getConnection();
            // 学院同步
            xySynchronization(connection);
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            // 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error(e.getMessage());
                    e.printStackTrace();
                }
            }
        }

    }

    @Override
    public void njSynchronization() {
        Connection connection = null;
        try {
            connection = OracleDbConnection.getConnection();
            njSynchronization(connection);;
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            // 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error(e.getMessage());
                    e.printStackTrace();
                }
            }
        }

    }

    @Override
    public void zySynchronization() {
        Connection connection = null;
        try {
            connection = OracleDbConnection.getConnection();
            zySynchronization(connection);
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            // 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error(e.getMessage());
                    e.printStackTrace();
                }
            }
        }

    }

    @Override
    public void bjSynchronization() {
        Connection connection = null;
        try {
            connection = OracleDbConnection.getConnection();
            bjSynchronization(connection);
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            // 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error(e.getMessage());
                    e.printStackTrace();
                }
            }
        }

    }

    @Override
    public void studentSynchronization() {
        Connection connection = null;
        try {
            connection = OracleDbConnection.getConnection();
            studentSynchronization(connection);
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            // 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error(e.getMessage());
                    e.printStackTrace();
                }
            }
        }

    }

    @Override
    public void teacherSynchronization() {
        Connection connection = null;
        try {
            connection = OracleDbConnection.getConnection();
            teacherSynchronization(connection);
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            // 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error(e.getMessage());
                    e.printStackTrace();
                }
            }
        }

    }

    @Override
    public void phoneSynchronization() {
        Connection connection = null;
        try {
            // 获取Oracle数据库连接
            connection = OracleDbConnection.getConnection();
            // 学生手机号同步
            studentPhoneSynchronization(connection);
            // 教师手机号同步
            teacherPhoneSynchronization(connection);
        }  catch (SQLException e) {
            log.error(e.getMessage());
            e.printStackTrace();
            // 处理异常
        } finally {
            // 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error(e.getMessage());
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 学院同步
     * @return
     */
    private boolean xySynchronization(Connection connection) {
        try {
            ArrayList<LinYiUniversityXy> linYiUniversityXys = new ArrayList<>();
            // 从Oracle数据库查询数据
            String query = "SELECT * FROM sjgx.\"dws_jcsj_bmjbxx_df\"";
            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {
                // 提取数据
                while (rs.next()) {
                    LinYiUniversityXy universityXy = new LinYiUniversityXy();
                    // 提取数据并处理，例如打印出来
                    // 提取数据并设置到实体类字段中
                    universityXy.setDws_zjid(rs.getString("dws_zjid"));
                    universityXy.setBmbh(rs.getString("bmbh"));
                    universityXy.setBmmc(rs.getString("bmmc"));
                    universityXy.setSjbmbh(rs.getString("sjbmbh"));
                    universityXy.setSjbmmc(rs.getString("sjbmmc"));
                    universityXy.setSfjxbm(rs.getString("sfjxbm"));
                    universityXy.setCxbs(rs.getString("cxbs"));
                    universityXy.setPx(rs.getString("px"));
                    universityXy.setDws_rksj(rs.getString("dws_rksj"));
                    linYiUniversityXys.add(universityXy);
                }
            }

            // 提取劳动教育平台的数据
            List<ProXy> proXyList = proXyMapper.selectProXyList(null);
            for (LinYiUniversityXy universityXy : linYiUniversityXys) {
                for (ProXy proXy : proXyList) {
                    if (universityXy.getDws_zjid().equals(proXy.getUniversityId())) {
                        if (needUpdate(universityXy,proXy)) {
                            ProXy proXyNeed = new ProXy();
                            proXyNeed.setId(proXy.getId());
                            proXyNeed.setUniversityId(universityXy.getDws_zjid());
                            proXyNeed.setNumber(universityXy.getBmbh());
                            proXyNeed.setName(universityXy.getBmmc());
                            proXyNeed.setSort(Long.parseLong(universityXy.getPx()));
                            proXyNeed.setStatus(universityXy.getCxbs());
                            proXyMapper.updateProXy(proXyNeed);
                            break;
                        }
                    }
                }
            }

            // 劳动教育大学id
            HashSet<String> universityIds = new HashSet<>();
            proXyList.forEach( proXy -> {
                if (!StringUtils.isEmpty(proXy.getUniversityId())) {
                    universityIds.add(proXy.getUniversityId());
                }
            });

            // 交集外的新增
            linYiUniversityXys.forEach(universityXys -> {
                if (!universityIds.contains(universityXys.getDws_zjid())) {
                    ProXy proXyNeed = new ProXy();
                    proXyNeed.setId(String.valueOf(SnowFlake.nextId()));
                    proXyNeed.setUniversityId(universityXys.getDws_zjid());
                    proXyNeed.setpId("1");
                    proXyNeed.setNumber(universityXys.getBmbh());
                    proXyNeed.setName(universityXys.getBmmc());
                    proXyNeed.setSort(Long.parseLong(universityXys.getPx()));
                    proXyNeed.setStatus(universityXys.getCxbs());
                    proXyNeed.setCreateTime(new Date());
                    proXyMapper.insertProXy(proXyNeed);
                }
            });

        } catch (Exception e) {
            log.error(e.getMessage());
//            e.printStackTrace();
        }
        return true;
    }

    /**
     * 年级同步
     * @return
     */
    private boolean njSynchronization(Connection connection) {
        try {
            // 从Oracle数据库查询数据
            String query = "SELECT \"njdm\" FROM sjgx.\"dws_jcsj_bjjbxx_df\" WHERE \"sfqy\" = '启用' AND \"njdm\" >= '2021' GROUP BY \"njdm\" ORDER BY \"njdm\" ";
            List<String> njbh = new ArrayList<>();

            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {
                // 提取数据
                while (rs.next()) {
                    // 提取数据并设置到实体类字段中
                    njbh.add(rs.getString("njdm"));
                }
            }

            // 劳动教育年级
            List<String> njList = proNjMapper.selectProNjListGroupByNjName();
            // 新增的年级，没有交集的
            List<String> collectNjs = njbh.stream().filter(nj -> !njList.contains(nj)).collect(Collectors.toList());
            List<ProXy> proXyList = proXyMapper.selectProXyList(null);
            if (collectNjs.size() != 0) {
                proXyList.forEach(proXy -> {
                    collectNjs.forEach(nj -> {
                        ProNj proNj = new ProNj();
                        proNj.setId(String.valueOf(SnowFlake.nextId()));
                        proNj.setpId(proXy.getId());
                        proNj.setName(nj);
                        proNj.setCreateTime(new Date());
                        proNjMapper.insertProNj(proNj);
                    });
                });
            }

            List<String> xyIds = proXyMapper.selectIdsByNotInNj();
            xyIds.forEach(xyId -> {
                njbh.forEach(bh -> {
                    ProNj proNj = new ProNj();
                    proNj.setId(String.valueOf(SnowFlake.nextId()));
                    proNj.setpId(xyId);
                    proNj.setName(bh);
                    proNj.setCreateTime(new Date());
                    proNjMapper.insertProNj(proNj);
                });
            });

        } catch (Exception e) {
            log.error(e.getMessage());
//            e.printStackTrace();
        }
        return true;
    }

    /**
     * 专业同步
     * @return
     */
    private boolean zySynchronization(Connection connection) {
        try {
            // 从Oracle数据库查询数据
            String query = "SELECT a.* ,b.\"dws_zjid\" pId FROM sjgx.\"dws_jx_zyjbxx_df\" a LEFT JOIN sjgx.\"dws_jcsj_bmjbxx_df\" b ON b.\"bmbh\" = a.\"bmdm\"";
            List<LinYiUniversityZy> linYiUniversityZys = new ArrayList<>();

            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {

                // 提取数据
                while (rs.next()) {
                    LinYiUniversityZy universityZy = new LinYiUniversityZy();
                    // 提取数据并设置到实体类字段中
                    universityZy.setDws_zjid(rs.getString("dws_zjid"));
                    universityZy.setZybh(rs.getString("zybh"));
                    universityZy.setZymc(rs.getString("zymc"));
                    universityZy.setZyjc(rs.getString("zyjc"));
                    universityZy.setBmdm(rs.getString("bmdm"));
                    universityZy.setBmmc(rs.getString("bmmc"));
                    universityZy.setXz(rs.getString("xz"));
                    universityZy.setCc(rs.getString("cc"));
                    universityZy.setSfty(rs.getString("sfty"));
                    universityZy.setSjly(rs.getString("sjly"));
                    universityZy.setDws_rksj(rs.getString("dws_rksj"));
                    universityZy.setPID(rs.getString("PID"));
                    linYiUniversityZys.add(universityZy);
                }
            }

            ArrayList<LinYiUniversityZy> linYiUniversityZIES = new ArrayList<>();
            linYiUniversityZys.forEach(linYiUniversityZy -> {
                ProZy proZy = new ProZy();
                proZy.setUniversityId(linYiUniversityZy.getDws_zjid());
                List<ProZy> proZyList = proZyMapper.selectProZyList(proZy);
                if (proZyList.size() == 0) {
                    linYiUniversityZIES.add(linYiUniversityZy);
                } else {
                    ProZy proZy1 = proZyList.get(0);
                    if (!proZy1.getXyUniversityId().equals(linYiUniversityZy.getPID())) {
                        // 需要更改的pid
                        List<String> ids = proNjMapper.selectNjIdsByXyUniversityId(linYiUniversityZy.getPID());
                        // 需要更改的专业id
                        List<String> idEds = proNjMapper.selectNjIdsByPId(proZy1.getXyId());
                        for (int i = 0; i < ids.size(); i ++) {
                            ProZy proZy2 = new ProZy();
                            proZy2.setId(idEds.get(i));
                            proZy2.setpId(ids.get(i));
                            proZyMapper.updateProZy(proZy2);
                        }
                    }
                    if (needUpdate(linYiUniversityZy,proZy1)) {
                        ProZy proZy2 = new ProZy();
                        proZy2.setpId(proZy1.getId());
                        proZy2.setNumber(linYiUniversityZy.getZybh());
                        proZy2.setName(linYiUniversityZy.getZymc());
                        proZy.setStatus(linYiUniversityZy.getSfty());
                        proZyMapper.updateProZy(proZy2);
                    }
                }
            });

            if (linYiUniversityZIES.size() > 0) {
                zyInsert(linYiUniversityZys);
            }

        } catch (Exception e) {
            log.error(e.getMessage());
//            e.printStackTrace();
        }
        return true;
    }

    /**
     * 班级同步
     * @return
     */
    private boolean bjSynchronization(Connection connection) {
        try {
            // 从Oracle数据库查询数据
            String query = "SELECT * FROM sjgx.\"dws_jcsj_bjjbxx_df\" WHERE \"njdm\" >= '2021' ";
            List<LinYiUniversityBj> linYiUniversityBjs = new ArrayList<>();

            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {

                // 提取数据
                while (rs.next()) {
                    LinYiUniversityBj universityBj = new LinYiUniversityBj();
                    // 提取数据并设置到实体类字段中
                    universityBj.setDws_zjid(rs.getString("dws_zjid"));
                    universityBj.setNjdm(rs.getString("njdm"));
                    universityBj.setBjbh(rs.getString("bjbh"));
                    universityBj.setBjmc(rs.getString("bjmc"));
                    universityBj.setXqbh(rs.getString("xqbh"));
                    universityBj.setXqmc(rs.getString("xqmc"));
                    universityBj.setBmbh(rs.getString("bmbh"));
                    universityBj.setBmmc(rs.getString("bmmc"));
                    universityBj.setZybh(rs.getString("zybh"));
                    universityBj.setZymc(rs.getString("zymc"));
                    universityBj.setFdygh(rs.getString("fdygh"));
                    universityBj.setFdyxm(rs.getString("fdyxm"));
                    universityBj.setSfqy(rs.getString("sfqy"));
                    universityBj.setBjlb(rs.getString("bjlb"));
                    universityBj.setDws_rksj(rs.getString("dws_rksj"));
                    linYiUniversityBjs.add(universityBj);
                }
            }

            ArrayList<LinYiUniversityBj> linYiUniversityBjs1 = new ArrayList<>();
            linYiUniversityBjs.forEach(linYiUniversityBj -> {
                ProBj bj = new ProBj();
                bj.setUniversityId(linYiUniversityBj.getDws_zjid());
                List<ProBj> proBjs = proBjMapper.selectProBjList(bj);
                if (proBjs.size() == 0) {
                    linYiUniversityBjs1.add(linYiUniversityBj);
                } else {
                    ProBj bj2 = proBjs.get(0);
                    String id = proBjMapper.selectZyIdByNjNumberAndZyBh(linYiUniversityBj.getNjdm(),linYiUniversityBj.getZybh());
                    if (!bj2.getpId().equals(id)) {
                        ProBj bj1 = new ProBj();
                        bj1.setpId(id);
                        bj1.setId(bj2.getId());
                        proBjMapper.updateProBj(bj1);
                    }
                    if (needUpdate(linYiUniversityBj,bj2)) {
                        ProBj bj1 = new ProBj();
                        bj1.setNumber(linYiUniversityBj.getBjbh());
                        bj1.setName(linYiUniversityBj.getBjmc());
                        bj1.setStatus(linYiUniversityBj.getSfqy());
                        bj1.setId(bj2.getId());
                        proBjMapper.updateProBj(bj1);
                    }
                }
            });

            // 新增
            bjInsert(linYiUniversityBjs1);

        } catch (Exception e) {
            log.error(e.getMessage());
//            e.printStackTrace();
        }
        return true;
    }

    /**
     * 学生同步
     * @return
     */
    private boolean studentSynchronization(Connection connection) {
        try {
            // 从Oracle数据库查询数据
            String query = "SELECT * FROM sjgx.\"dws_xs_xsjbxx_df\" WHERE \"nj\" >= '2021' and \"pycc\" in ('本科','3+2本科','3+4本科','高职本科','专升本')";
            List<LinYiUniversityStudent> linYiUniversityStudents = new ArrayList<>();

            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {

                // 提取数据
                while (rs.next()) {
                    LinYiUniversityStudent student = new LinYiUniversityStudent();
                    // 提取数据并设置到实体类字段中
                    student.setDws_zjid(rs.getString("dws_zjid"));
                    student.setXh(rs.getString("xh"));
                    student.setXm(rs.getString("xm"));
                    student.setXz(rs.getString("xz"));
                    student.setNj(rs.getString("nj"));
                    student.setXsdqzt(rs.getString("xsdqzt"));
                    student.setYwxj(rs.getString("ywxj"));
                    student.setSfzx(rs.getString("sfzx"));
                    student.setSflx(rs.getString("sflx"));
                    student.setBjbh(rs.getString("bjbh"));
                    student.setBjmc(rs.getString("bjmc"));
                    student.setZybh(rs.getString("zybh"));
                    student.setZymc(rs.getString("zymc"));
                    student.setBmbh(rs.getString("bmbh"));
                    student.setBmmc(rs.getString("bmmc"));
                    student.setSzxq(rs.getString("szxq"));
                    student.setSfzjlx(rs.getString("sfzjlx"));
                    student.setSfzjh(rs.getString("sfzjh"));
                    student.setSjhm(rs.getString("sjhm"));
                    student.setXb(rs.getString("xb"));
                    student.setCsrq(rs.getString("csrq"));
                    student.setJg(rs.getString("jg"));
                    student.setKsh(rs.getString("ksh"));
                    student.setSyddm(rs.getString("syddm"));
                    student.setSyd(rs.getString("syd"));
                    student.setMz(rs.getString("mz"));
                    student.setGj(rs.getString("gj"));
                    student.setHyzk(rs.getString("hyzk"));
                    student.setXyzj(rs.getString("xyzj"));
                    student.setZzmm(rs.getString("zzmm"));
                    student.setJkzk(rs.getString("jkzk"));
                    student.setRxny(rs.getString("rxny"));
                    student.setPycc(rs.getString("pycc"));
                    student.setXslb(rs.getString("xslb"));
                    student.setDws_rksj(rs.getString("dws_rksj"));
                    linYiUniversityStudents.add(student);
                }
            }

            ArrayList<LinYiUniversityStudent> linYiUniversityStudentList = new ArrayList<>();
            for (LinYiUniversityStudent linYiUniversityStudent : linYiUniversityStudents) {
                ProUser proUser = new ProUser();
                proUser.setUniversityId(linYiUniversityStudent.getDws_zjid());
                List<ProUser> proUsers = proUserMapper.selectProUserList(proUser);
                if (proUsers.size() == 0) {
                    linYiUniversityStudentList.add(linYiUniversityStudent);
                } else {
                    ProUser proUser1 = proUsers.get(0);
                    // 只查询班级id就可以，校验是否有变化,需要插入的班级
                    // 一次性查询 学院id、年级id、专业id、班级id
                    ProUser needStudent = proUserMapper.selectXyIdNjIdZyIdBjId(linYiUniversityStudent.getBmbh(),
                            linYiUniversityStudent.getZybh(), linYiUniversityStudent.getBjbh());
                    if (!proUser1.getClId().equals(needStudent.getClId())) {
                        needStudent.setUserId(proUser1.getUserId());
                        proUserMapper.updateProUser(needStudent);
                    }

                    if (needUpdate(linYiUniversityStudent,proUser1)) {
                        ProUser proUser2 = new ProUser();
                        proUser2.setUserId(proUser1.getUserId());
                        proUser2.setNickName(linYiUniversityStudent.getXm());
                        proUser2.setName(linYiUniversityStudent.getXm());
                        proUser2.setPhone(linYiUniversityStudent.getSjhm());
                        proUser2.setStNumber(linYiUniversityStudent.getXh());
                        proUser.setSex(linYiUniversityStudent.getXb().equals("男") ? "0" : "1");
                        proUserMapper.updateProUser(proUser2);
                    }
                }
            }

            studentInsert(linYiUniversityStudentList);
        } catch (Exception e) {
            log.error(e.getMessage());
//            e.printStackTrace();
        }
        return true;
    }

    /**
     * 教师同步
     * @return
     */
    private boolean teacherSynchronization(Connection connection) {
        try {
            // 从Oracle数据库查询数据
            String query = "SELECT * FROM sjgx.\"dws_jzg_jzgjbxx_df\"";
            List<LinYiUniversityTeacher> linYiUniversityTeachers = new ArrayList<>();

            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {

                // 提取数据
                while (rs.next()) {
                    LinYiUniversityTeacher teacher = new LinYiUniversityTeacher();
                    // 提取数据并设置到实体类字段中
                    teacher.setDwsZjid(rs.getString("dws_zjid"));
                    teacher.setGh(rs.getString("gh"));
                    teacher.setXm(rs.getString("xm"));
                    teacher.setXmpy(rs.getString("xmpy"));
                    teacher.setXb(rs.getString("xb"));
                    teacher.setCsrq(rs.getString("csrq"));
                    teacher.setNl(rs.getString("nl"));
                    teacher.setCsd(rs.getString("csd"));
                    teacher.setMz(rs.getString("mz"));
                    teacher.setJg(rs.getString("jg"));
                    teacher.setZjlx(rs.getString("zjlx"));
                    teacher.setZjhm(rs.getString("zjhm"));
                    teacher.setYhkh(rs.getString("yhkh"));
                    teacher.setSjhm(rs.getString("sjhm"));
                    teacher.setZzmm(rs.getString("zzmm"));
                    teacher.setHyzk(rs.getString("hyzk"));
                    teacher.setJrdtsj(rs.getString("jrdtsj"));
                    teacher.setJkzk(rs.getString("jkzk"));
                    teacher.setTxdz(rs.getString("txdz"));
                    teacher.setDzxx(rs.getString("dzxx"));
                    teacher.setJtzz(rs.getString("jtzz"));
                    teacher.setHjszd(rs.getString("hjszd"));
                    teacher.setJjlxr(rs.getString("jjlxr"));
                    teacher.setJjlxrdh(rs.getString("jjlxrdh"));
                    teacher.setHkxz(rs.getString("hkxz"));
                    teacher.setYjxk(rs.getString("yjxk"));
                    teacher.setEjxk(rs.getString("ejxk"));
                    teacher.setCjgzrq(rs.getString("cjgzrq"));
                    teacher.setJdgl(rs.getString("jdgl"));
                    teacher.setZgxl(rs.getString("zgxl"));
                    teacher.setZgxw(rs.getString("zgxw"));
                    teacher.setLxrq(rs.getString("lxrq"));
                    teacher.setSzbmbh(rs.getString("bmbh"));
//                    teacher.setSzmbmc(rs.getString("mbmc"));
                    teacher.setGwmc(rs.getString("gwmc"));
                    teacher.setGwlb(rs.getString("gwlb"));
                    teacher.setGwlx(rs.getString("gwlx"));
                    teacher.setGwjb(rs.getString("gwjb"));
                    teacher.setZwlb(rs.getString("zwlb"));
                    teacher.setZwjb(rs.getString("zwjb"));
                    teacher.setZc(rs.getString("zc"));
                    teacher.setZcjb(rs.getString("zcjb"));
                    teacher.setSfwfdy(rs.getString("sfwfdy"));
                    teacher.setSfwbksjx(rs.getString("sfwbksjx"));
                    teacher.setSfszkjs(rs.getString("sfszkjs"));
                    teacher.setYmxzzsbh(rs.getString("ymxzzsbh"));
                    teacher.setSpcc(rs.getString("spcc"));
                    teacher.setSfzb(rs.getString("sfzb"));
                    teacher.setSfzrjs(rs.getString("sfzrjs"));
                    teacher.setRyly(rs.getString("ryly"));
                    teacher.setRyfl(rs.getString("ryfl"));
                    teacher.setRyzt(rs.getString("ryzt"));
                    teacher.setDwsRksj(rs.getString("dws_rksj"));
                    linYiUniversityTeachers.add(teacher);
                }
            }

            ArrayList<LinYiUniversityTeacher> linYiUniversityTeacherList = new ArrayList<>();
            linYiUniversityTeachers.forEach(linYiUniversityTeacher -> {
                SysUser sysUser = new SysUser();
                sysUser.setUniversityId(linYiUniversityTeacher.getDwsZjid());
                List<SysUser> sysUsers = sysUserApiMapper.selectSysUserList(sysUser);
                if (sysUsers.size() == 0) {
                    linYiUniversityTeacherList.add(linYiUniversityTeacher);
                } else {
                    SysUser sysUser1 = sysUsers.get(0);
                    // 通过学院编号查询学院id
                    String xyId = proXyMapper.selectXyIdByXyBh(linYiUniversityTeacher.getSzbmbh());
                    if (needUpdate(linYiUniversityTeacher,sysUser1,xyId)) {
                        SysUser sysUser2 = new SysUser();
                        sysUser2.setUniversityId(linYiUniversityTeacher.getDwsZjid());
                        sysUser2.setUserName(linYiUniversityTeacher.getXm());
                        sysUser2.setNickName(linYiUniversityTeacher.getXm());
                        sysUser2.setEmail(linYiUniversityTeacher.getDzxx());
                        sysUser2.setPhonenumber(linYiUniversityTeacher.getSjhm());
                        sysUser2.setSex(linYiUniversityTeacher.getXb().equals("男") ? "0" : "1");
                        sysUser2.setStatus(linYiUniversityTeacher.getRyzt().equals("在职") ? "0" : "1");
                        sysUser2.setDelFlag("0");
                        sysUser2.setJsNumber(linYiUniversityTeacher.getGh());
                        sysUser2.setXyId(xyId);
                        sysUser2.setUserId(sysUser1.getUserId());
                        sysUserApiMapper.updateSysUser(sysUser2);
                    }
                }
            });
            teacherInsert(linYiUniversityTeacherList);
        } catch (Exception e) {
            log.error(e.getMessage());
//            e.printStackTrace();
        }
        return true;
    }

    // 学生手机号同步
    private void studentPhoneSynchronization(Connection connection) {
        try {
            // 从Oracle数据库查询数据
            String query = "SELECT \"dws_zjid\",\"sjhm\" FROM sjgx.\"dws_xs_xsjbxx_df\" WHERE \"nj\" >= '2021' AND (\"xsdqzt\" != '毕业' OR \"xslb\" != '研究生')";
            ArrayList<LinYiUniversityPhoneNumber> linYiUniversityPhoneNumbers = new ArrayList<>();

            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {

                // 提取数据
                while (rs.next()) {
                    LinYiUniversityPhoneNumber linYiUniversityPhoneNumber = new LinYiUniversityPhoneNumber();
                    // 提取数据并设置到实体类字段中
                    linYiUniversityPhoneNumber.setDws_zjid(rs.getString("dws_zjid"));
                    linYiUniversityPhoneNumber.setSjhm(rs.getString("sjhm"));
                    linYiUniversityPhoneNumbers.add(linYiUniversityPhoneNumber);
                }
            }

            // 从劳动教育库中取出所有数据
            List<ProUser> proUsers = proUserMapper.selectPhoneAndUniversityId();
            ArrayList<ProUser> proUsersNeedUpdatePhoneList = new ArrayList<>();
            for (ProUser proUser : proUsers) {
                if (StringUtils.isEmpty(proUser.getUniversityId())) {
                    continue;
                }
                for (LinYiUniversityPhoneNumber linYiUniversityPhoneNumber : linYiUniversityPhoneNumbers) {
                    if (StringUtils.isEmpty(linYiUniversityPhoneNumber.getDws_zjid())
                    || StringUtils.isEmpty(proUser.getUniversityId())
                    || StringUtils.isEmpty(linYiUniversityPhoneNumber.getSjhm())
                    || StringUtils.isEmpty(proUser.getPhone())) {
                        continue;
                    }
                    if (proUser.getUniversityId().equals(linYiUniversityPhoneNumber.getDws_zjid())) {
                        String last11phone = linYiUniversityPhoneNumber.getSjhm()
                                .substring(linYiUniversityPhoneNumber.getSjhm().length() - 11);
                        if (!proUser.getPhone().equals(last11phone)) {
                            proUser.setPhone(last11phone);
                            proUsersNeedUpdatePhoneList.add(proUser);
                            break;
                        }
                    }
                }
            }

            if (proUsersNeedUpdatePhoneList.size() > 0) {
                proUsersNeedUpdatePhoneList.forEach(proUser -> {
                    proUserMapper.updateProUser(proUser);
                });
            }
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    // 教师手机号同步
    private void teacherPhoneSynchronization(Connection connection) {
        try {
            // 从Oracle数据库查询数据
            String query = "SELECT \"dws_zjid\",\"sjhm\" FROM sjgx.\"dws_jzg_jzgjbxx_df\" WHERE \"ryzt\" = '在职'";
            ArrayList<LinYiUniversityPhoneNumber> linYiUniversityPhoneNumbers = new ArrayList<>();

            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {

                // 提取数据
                while (rs.next()) {
                    LinYiUniversityPhoneNumber linYiUniversityPhoneNumber = new LinYiUniversityPhoneNumber();
                    // 提取数据并设置到实体类字段中
                    linYiUniversityPhoneNumber.setDws_zjid(rs.getString("dws_zjid"));
                    linYiUniversityPhoneNumber.setSjhm(rs.getString("sjhm"));
                    linYiUniversityPhoneNumbers.add(linYiUniversityPhoneNumber);
                }
            }

            // 从劳动教育库中取出所有数据
            List<SysUser> sysUsers = sysUserApiMapper.selectPhoneAndUniversityId();
            ArrayList<SysUser> sysUsersNeedUpdatePhone = new ArrayList<>();
            for (SysUser user : sysUsers) {
                if (StringUtils.isEmpty(user.getUniversityId())) {
                    continue;
                }
                for (LinYiUniversityPhoneNumber linYiUniversityPhoneNumber : linYiUniversityPhoneNumbers) {
                    if (StringUtils.isEmpty(linYiUniversityPhoneNumber.getDws_zjid())
                            || StringUtils.isEmpty(user.getUniversityId())
                    || StringUtils.isEmpty(user.getPhonenumber())
                    || StringUtils.isEmpty(linYiUniversityPhoneNumber.getSjhm())) {
                        continue;
                    }
                    if (user.getUniversityId().equals(linYiUniversityPhoneNumber.getDws_zjid())) {
                        if (linYiUniversityPhoneNumber.getSjhm().length() > 11) {
                            String sjhm = linYiUniversityPhoneNumber.getSjhm();
                            String last11phone = sjhm
                                    .substring(sjhm.length() - 11);
                            linYiUniversityPhoneNumber.setSjhm(last11phone);
                        }
                        if (!user.getPhonenumber().equals(linYiUniversityPhoneNumber.getSjhm())) {
                            user.setPhonenumber(linYiUniversityPhoneNumber.getSjhm());
                            sysUsersNeedUpdatePhone.add(user);
                            break;
                        }
                    }
                }
            }

            if (sysUsersNeedUpdatePhone.size() > 0) {
                sysUsersNeedUpdatePhone.forEach(user -> {
                    sysUserApiMapper.updateSysUser(user);
                });
            }
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }


    // 学院：比较两个实体的特定属性是否一致
    private boolean needUpdate(LinYiUniversityXy universityXy, ProXy proXy) {
        if (!universityXy.getBmbh().equals(proXy.getNumber())) {
            return true;
        }

        if (!proXy.getName().equals(universityXy.getBmmc())) {
            return true;
        }

        if (!proXy.getSort().toString().equals(universityXy.getPx())) {
            return true;
        }

        return !proXy.getStatus().equals(universityXy.getCxbs());
    }

    // 专业：比较两个实体的特定属性是否一致
    private boolean needUpdate(LinYiUniversityZy linYiUniversityZy, ProZy proZy) {

        if (!proZy.getNumber().equals(linYiUniversityZy.getZybh())) {
            return true;
        }

        if (!proZy.getName().equals(linYiUniversityZy.getZymc())) {
            return true;
        }

        if (StringUtils.isEmpty(proZy.getStatus())) {
            return false;
        } else {
            return !proZy.getStatus().equals(linYiUniversityZy.getSfty());
        }
    }

    // 班级
    private boolean needUpdate(LinYiUniversityBj linYiUniversityBj,ProBj proBj) {
        if (!linYiUniversityBj.getBjbh().equals(proBj.getNumber())) {
            return true;
        }

        if (!linYiUniversityBj.getBjmc().equals(proBj.getName())) {
            return true;
        }

        if (StringUtils.isEmpty(proBj.getStatus())) {
            return false;
        } else {
            return !proBj.getStatus().equals(linYiUniversityBj.getSfqy());
        }
    }

    // 学生：校验
    private boolean needUpdate(LinYiUniversityStudent linYiUniversityStudent,ProUser proUser) {
        if (!linYiUniversityStudent.getXm().equals(proUser.getNickName())) {
            return true;
        }

        if (!linYiUniversityStudent.getSjhm().equals(proUser.getPhone())) {
            return true;
        }

        if (!linYiUniversityStudent.getXm().equals(proUser.getName())) {
            return true;
        }

        if (!linYiUniversityStudent.getXh().equals(proUser.getStNumber())) {
            return true;
        }

        return !(linYiUniversityStudent.getXb().equals("男") ? "0" : "1").equals(proUser.getSex());

    }

    // 教师：校验
    private boolean needUpdate(LinYiUniversityTeacher linYiUniversityTeacher,SysUser sysUser,String xyId) {
        if (!sysUser.getXyId().equals(xyId)) {
            return true;
        }

        if (!sysUser.getUserName().equals(linYiUniversityTeacher.getXm())) {
            return true;
        }

        if (!sysUser.getNickName().equals(linYiUniversityTeacher.getXm())) {
            return true;
        }

        if (!sysUser.getEmail().equals(linYiUniversityTeacher.getDzxx())) {
            return true;
        }

        if (!sysUser.getPhonenumber().equals(linYiUniversityTeacher.getSjhm())) {
            return true;
        }

        if (!sysUser.getSex().equals(linYiUniversityTeacher.getXb().equals("男") ? "0" : "1")) {
            return true;
        }

        if (!sysUser.getStatus().equals(linYiUniversityTeacher.getRyzt().equals("在职") ? "0" : "1")) {
            return true;
        }

        return !sysUser.getJsNumber().equals(linYiUniversityTeacher.getGh());
    }

    // 专业：新增
    private void zyInsert(List<LinYiUniversityZy> universityZyList) {
        universityZyList.forEach(linYiUniversityZy -> {
            // 查询年级
            List<String> njIds = proNjMapper.selectProNjIdsByUniversityXyId(linYiUniversityZy.getPID());

            // 遍历年级，新增专业
            njIds.forEach(proNjEntry -> {
                // 每个年级新增专业
                ProZy proZy = new ProZy();
                proZy.setId(String.valueOf(SnowFlake.nextId()));
                proZy.setUniversityId(linYiUniversityZy.getDws_zjid());
                proZy.setpId(proNjEntry);
                proZy.setNumber(linYiUniversityZy.getZybh());
                proZy.setName(linYiUniversityZy.getZymc());
                proZy.setStatus(linYiUniversityZy.getSfty());
                proZy.setCreateTime(new Date());
                proZyMapper.insertProZy(proZy);
            });
        });
    }

    // 班级：新增
    private void bjInsert(List<LinYiUniversityBj> linYiUniversityBjs) {
        linYiUniversityBjs.forEach(linYiUniversityBj -> {
            try {
                String id = proZyMapper.selectIdByZYBHAndNJDM(linYiUniversityBj.getZybh(),linYiUniversityBj.getNjdm());
                ProBj bj = new ProBj();
                bj.setId(String.valueOf(SnowFlake.nextId()));
                bj.setUniversityId(linYiUniversityBj.getDws_zjid());
                bj.setpId(id);
                bj.setNumber(linYiUniversityBj.getBjbh());
                bj.setName(linYiUniversityBj.getBjmc());
                bj.setStatus(linYiUniversityBj.getSfqy());
                bj.setCreateTime(new Date());
                int row = proBjMapper.insertProBj(bj);
                if (row == 0) {
                    log.error("班级插入失败" + bj);
                }
            } catch (Exception e) {
                log.error("班级插入失败：" + e);
            }
        });
    }

    // 学生：新增
    private void studentInsert(List<LinYiUniversityStudent> linYiUniversityStudents) {
        linYiUniversityStudents.forEach(linYiUniversityStudent -> {
            try {
                // 一次性查询 学院id、年级id、专业id、班级id
                ProUser proUser = proUserMapper.selectXyIdNjIdZyIdBjId(linYiUniversityStudent.getBmbh(),
                        linYiUniversityStudent.getZybh(),linYiUniversityStudent.getBjbh());
                proUser.setUserId(String.valueOf(SnowFlake.nextId()));
                proUser.setUniversityId(linYiUniversityStudent.getDws_zjid());
                proUser.setXxId("1");
                proUser.setNickName(linYiUniversityStudent.getXm());
                proUser.setPhone(linYiUniversityStudent.getSjhm());
                proUser.setName(linYiUniversityStudent.getXm());
                proUser.setStNumber(linYiUniversityStudent.getXh());
                proUser.setCreateTime(new Date());
                proUser.setSex(linYiUniversityStudent.getXb().equals("男") ? "0" : "1");
                proUser.setPassword(SecurityUtil.md5enc("LDJYxsd2024"));
                int row = proUserMapper.insertProUser(proUser);
                if (row == 0) {
                    log.error("学生插入失败" + proUser);
                }
            } catch (Exception e) {
                log.error("学生插入失败:" + e);
            }

        });
    }

    // 教师新增
    private void teacherInsert(List<LinYiUniversityTeacher> linYiUniversityTeachers) {
        linYiUniversityTeachers.forEach(linYiUniversityTeacher -> {
            try {
                String id = proXyMapper.selectXyIdByXyBh(linYiUniversityTeacher.getSzbmbh());
                String initPassword = iSysConfigService.selectConfigByKey("sys.user.initPassword");
                SysUser sysUser = new SysUser();
                sysUser.setUniversityId(linYiUniversityTeacher.getDwsZjid());
                sysUser.setUserName(linYiUniversityTeacher.getXm());
                sysUser.setNickName(linYiUniversityTeacher.getXm());
                sysUser.setUserType("01");
                sysUser.setEmail(linYiUniversityTeacher.getDzxx());
                sysUser.setPhonenumber(linYiUniversityTeacher.getSjhm());
                sysUser.setSex(linYiUniversityTeacher.getXb().equals("男") ? "0" : "1");
                sysUser.setPassword(SecurityUtils.encryptPassword(initPassword));
                sysUser.setUnPassword(SecurityUtil.md5enc(initPassword));
                sysUser.setStatus(linYiUniversityTeacher.getRyzt().equals("在职") ? "0" : "1");
                sysUser.setDelFlag("0");
                sysUser.setJsNumber(linYiUniversityTeacher.getGh());
                sysUser.setXyId(id);
                int row = sysUserApiMapper.insertSysUser(sysUser);
                if (row == 0) {
                    log.error("教师插入失败" + sysUser);
                }
            } catch (Exception e) {
                log.error("教师插入失败：" + e);
            }
        });
    }

}
